﻿USE master
IF EXISTS (SELECT * FROM sysdatabases WHERE name = 'QuanLySieuThi')
	DROP DATABASE QuanLySieuThi
go
	create database QuanLySieuThi
go
	use QuanLySieuThi
go
create table LoaiHang
(
	LoaiHangID	int primary key identity,
	MaLoaiHang	varchar(50)unique not null,
	TenLoaiHang	nvarchar(200) not null,
	GhiChu	nvarchar(200),
	Deleted	bit
)
go
create table NhomHang
(
	NhomHangID	int primary key identity,
	MaNhomHang	varchar(50) unique not null,
	MaLoaiHang	varchar(50) not null,
	TenLoaiHang	nvarchar(200) not null,
	TenNhomHang	nvarchar(200) not null,
	GhiChu	nvarchar(200),
	Deleted	bit
)
go 
create table Thue
(
	ThueID	int	primary key identity,
	MaThue	varchar(50) unique	Not Null,
	TenThue	nvarchar(200)	Not Null,
	GiaTriThue	float	Not Null,
	GhiChu	nvarchar(50),	
	Deleted	bit
)
go
create table HangHoa
(
	HangHoaID	int primary key identity,
	MaHangHoa	varchar(50) unique	Not Null,
	MaNhomHang	varchar(50)	Not Null,
	TenNhomHang	nvarchar(200) not null,
	TenHangHoa	nvarchar(200)	not null,
	MaDonViTinh	varchar(50)	not null,
	TenDonViTinh nvarchar(200) not null,
	GiaNhap	float	not null,
	GiaBanBuon	float,
	GiaBanLe	float,	
	MaThue	varchar(50)	Not Null,
	GiaTriThue	float,
	MucDatHang	float,
	MucTonToiThieu	float	,
	LinkImage nvarchar(200),
	GhiChu	nvarchar(200),
	Deleted	bit	
)
go
create table ChinhSachGiaHangHoa
(
	ChinhSachGiaHHID	int	primary key identity,
	MaChinhSachGiaHangHoa	varchar(50) unique	Not Null,
	NgayLap Datetime,
	NgayBatDau	Datetime,	
	NgayKetThuc	Datetime,
	GhiChu	nvarchar(200),	
	Deleted	bit	
)
go
create table ChiTietChinhSachGiaHangHoa
(
	ChiTietChinhSachGiaHangHoaID int primary key identity,
	MaChinhSachGiaHangHoa	varchar(50) Not Null,
	MaHangHoa	varchar(50)	Not Null,
	TenHangHoa nvarchar(200),
	Soluong	int,
	PhanTramGiaBanBuon	float	,
	PhanTramGiaBanLe	float	,
	GhiChu	nvarchar(200)	,
	Deleted	bit	
)
go
create table CapNhatGiaHangHoa
(
	CapNhatGiaHangHoaID	int primary key identity,
	MaCapNhatGiaHangHoa	varchar(50) unique	Not Null,
	NgayCapNhat	datetime	Not Null,
	NhanVien	varchar(50)	Not Null,
	GhiChu	nvarchar(200)	,
	Deleted	bit	
)
go 
create table ChiTietCapNhatGiaHangHoa
(
	ChiTietCapNhatGiaHangHoaID int primary key identity,
	MaCapNhatGiaHangHoa	varchar(50)	Not Null,
	MaHangHoa	varchar(50)	Not Null,
	TenHangHoa nvarchar(200),
	GiaNhap	float	Not Null,
	GiaBanBuon	float	Not Null,
	GiaBanLe	float	Not Null,
	Deleted	bit	
)
go 
create table KhoHang
(
	KhoHangID	int	primary key identity,
	MaKho	varchar(50) unique	Not Null,
	TenKho	nvarchar(200)	Not Null,
	DiaChi	nvarchar(200),	
	DienThoai	nvarchar(20)	,
	TenNhanVien nvarchar(200),
	MaNhanVien	varchar(50)	Not Null,
	GhiChu	nvarchar(200),
	Deleted	bit	
)
go 
create table ChiTietKhoHang
(
	ChiTietKhoHangID int primary key identity,
	MaKho	varchar(50) not null,
	TenKho  nvarchar(200),
	MaHangHoa	varchar(50)not null,
	TenHangHoa nvarchar(200),
	SoLuong	float not null,
	NgayNhap	datetime,
	HanSuDung	datetime,
	MaDonViTinh varchar(50),
	TenDonViTinh nvarchar(200),
	GhiChu	nvarchar(200),
	Deleted	bit
)
go 
create table KhachHang
(
	KhachHangID	int	primary key identity,
	MaKhachHang	nvarchar(50) unique	Not Null,
	Ten	nvarchar(200)	Not Null,
	GioiTinh bit,
	DiaChi	nvarchar(200)	,
	DienThoaiCD	varchar(20),
	DienThoaiDD	varchar(20),
	Fax	nvarchar(20),
	Email	nvarchar(50),
	MST	nvarchar(20),
	CongTy	nvarchar(200)	,
	NgaySinh	Datetime,
	MaVung	int	,
	NgayThamGia	Datetime	,
	GiaoDichCuoi	Datetime,	
	website	varchar(200),
	DuNo	float,
	HanMucThanhToan	float,
	LinkImage nvarchar(200),
	GhiChu	nvarchar(200),
	Deleted	bit	
)
GO
create table ChiTietCongNoKH
(
	ChiTietCongNoKhachHangID int identity(1,1) primary key,
	MaKhachHang varchar(50),
	TenKhachHang nvarchar(200),
	MaKho	varchar(50),
	TenKho nvarchar(200),
	DuNo	float,
	Deleted	bit
)
go
create table NhaCungCap
(
	NhaCungCapID int	primary key identity,
	MaNhaCungCap nvarchar(50) unique	Not Null,
	TenNhaCungCap	nvarchar(200)	Not Null,
	NoHienThoi float,
	DiaChi	nvarchar(200),
	DienThoai	nvarchar(20),
	Email	nvarchar(50),
	Website	varchar(200),
	Fax	nvarchar(20),
	NguoiLienHe	nvarchar(200),
	MaSoThue	nvarchar(20),
	LinkImage nvarchar(200),
	GhiChu	nvarchar(200),
	Deleted	bit	
)
GO
create table ChiTietCongNoNCC
(
	ChiTietCongNoNhaCungCapID	int identity(1,1) primary key,
	MaNhaCungCap	varchar(50),
	MaKho	varchar(50),
	TenKho nvarchar(200),
	DuNo	float,
	Deleted	bit
)
go
create table ChiTietHangHoaNhaCungCap
(
	ChiTietHangHoaNhaCungCapID	int identity(1,1) primary key,
	MaNhaCungCap	varchar(50),
	TenNhaCungCap nvarchar(200),
	MaHangHoa	varchar(50),
	TenHangHoa	nvarchar(200),
	MaDonViTinh varchar(50),
	TenDonViTinh nvarchar(200),
	GhiChu	nvarchar(200),
	Deleted	bit
)
go
create table PhongBan
(
	PhongBanID	int	primary key identity,
	MaPhongBan	varchar(50) unique	Not Null,
	TenPhongBan	nvarchar(200)	Not Null,
	GhiChu	nvarchar(200),
	Deleted	bit	
)
go
create table NhanVien
(
	NhanVienID	int	primary key identity,
	MaNhanVien	varchar(50) unique	Not Null,
	TenNhanVien	nvarchar(200)Not Null,
	NgaySinh datetime,	
	GioiTinh bit,
	MaPhongBan	varchar(50)	Not Null,
	TenPhongBan	nvarchar(200),
	DCThuongTru	nvarchar(200),
	DCTamTru	nvarchar(200),
	DienThoaiCD	varchar(20),
	DienThoaiDD	varchar(20),
	Email	nvarchar(50),
	CMND	nvarchar(20) Not Null,
	NgayCap	datetime,
	NoiCap	nvarchar(200),	
	LinkImage nvarchar(200),
	GhiChu	nvarchar(200),
	Deleted	bit	
)
go 
create table NhomTKKeToan
(
	NhomTKKeToanID	int	primary key identity,
	MaNhomTKKeToan	varchar(50) Not Null,
	TenNhomTaiKhoan	nvarchar(200)	Not Null,
	GhiChu	nvarchar(200),
	Deleted	bit	
)
go
create table TKKeToan
(
	TKKeToanID	int	primary key identity,
	MaTKKeToan	varchar(50) Not Null,
	TenTaiKhoan	nvarchar(200) Not Null,
	MaNhomTKKeToan	varchar(50),
	GhiChu	nvarchar(200),
	Deleted	bit	
)
go
create table DonDatHangKH
(
	DonDatHangKHID	int	primary key identity,
	MaDonDatHangKH	varchar(50) unique	Not Null,
	NgayDonHang	Datetime,
	MaKhachHang	varchar(50)	 not null,
	TenKhachHang	nvarchar(200),
	NoHienThoi	float,
	TrangThaiDonDatHang	nvarchar(20),
	NgayNhapDuKien	Datetime,
	HinhThucThanhToan	nvarchar(20),
	MaKho	varchar(50),
	TenKho nvarchar(200),
	MaNhanVien	varchar(50),
	TenNhanVien nvarchar(200),
	MaTienTe	varchar(50),
	GhiChu	nvarchar(200)	,
	Deleted	bit	
)
go 
create table ChiTietDonHangKH
(
	ChiTietDonHangKHID int primary key identity,
	MaDonDatHangKH	varchar(50) not null,
	MaHangHoa	varchar(50) not null,
	TenHangHoa nvarchar(200),
	SoLuong	float	Not Null,
	DonGia	float,
	PhanTramKhuyenMai float,
	ThanhTien	float,
	Thue	float,
	HanSuDung datetime,
	GhiChu	nvarchar(200),
	Deleted	bit	
)
go
create table DonDatHangNCC
(
	DonDatHangNCCID	int	primary key identity,
	MaDonDatHangNCC	varchar(50)unique	Not Null,
	NgayDonHang	Datetime,
	MaNhaCungCap	varchar(50) not null,
	TenNhaCungCap	nvarchar(200),
	NoHienThoi	float,
	TrangThaiDonDatHang	nvarchar(20),
	NgayNhapDuKien	Datetime,
	HinhThucThanhToan	nvarchar(20),
	MaKho	varchar(50),
	TenKho nvarchar(200),
	MaNhanVien	varchar(50),
	TenNhanVien nvarchar(200),
	MaTienTe	varchar(50),
	Tygia float default 1,
	GhiChu	nvarchar(200),
	Deleted	bit	
)
go
create table ChiTietDonHangNCC
(
	ChiTietDonHangNCCID int primary key identity,
	MaDonDatHangNCC	varchar(50) not null,
	MaHangHoa	varchar(50) not null,
	TenHangHoa nvarchar(200),
	SoLuong	float	Not Null,
	DonGia	float,
	PhanTramChietKhau float,
	ThanhTien	float,
	Thue	float,
	GhiChu	nvarchar(200),
	Deleted	bit	
)
go
create table KhachHangTraLaiBanLe
(
	KhachHangTraLaiBanLeID	int identity(1,1),
	MaKhachHangTraLaiBanLe	varchar(50),
	MaKhachHang	varchar(50),
	TenKhachHang nvarchar(200),
	MaKho	varchar(50) null,
	TenKho  nvarchar(200),
	MaNhanVien	varchar(50) Null,
	TenNhanVien	nvarchar(200),
	NgayNhap	Datetime,
	NguoiTra	nvarchar(200),
	MaTienTe	varchar(50),
	TyGiaTienTe	float,
	TienBoiThuong float,
	GhiChu	nvarchar(200),
	Deleted	bit
)
go
create table ChiTietKhachHangTraLaiBanLe
(
	ChiTietKhachHangTraLaiBanLeID int	primary key identity,
	MaKhachHangTraLaiBanLe	varchar(50),
	MaHoaDonBanLe	varchar(50),
	MaHangHoa	varchar(50),
	TenHangHoa	nvarchar(200),
	SoLuong	int,
	PhanTramChietKhau	float,
	DonGia	float,
	ThanhTien float,
	Thue	float,
	HanSuDung	datetime,
	GhiChu	nvarchar(200),
	Deleted	bit
)
GO
create table KhachHangTraLaiBanBuon
(
	KhachHangTraLaiBanBuonID  int	primary key identity,
	MaKhachHangTraLaiBanBuon	varchar(50),
	NgayNhap	Datetime,
	MaKhachHang	varchar(50),
	TenKhachHang nvarchar(200),
	DuNo float,
	NguoiTra	nvarchar(200),
	MaKho	varchar(50) null,
	TenKho  nvarchar(200),
	MaNhanVien	varchar(50) Null,
	TenNhanVien	nvarchar(200) Null,
	MaTienTe	varchar(50),
	TyGiaTienTe	float,
	Thue float,
	TienBoiThuong	float,
	ThanhToanNgay	float,
	ThanhToanSauKhiLapPhieu	float,
	GhiChu	nvarchar(200),
	Deleted	bit
)
GO
create table ChiTietKhachHangTraLaiBanBuon
(
	ChiTietKhachHangTraLaiBanBuonID int	primary key identity,
	MaKhachHangTraLai	varchar(50),
	MaHoaDonBanBuon	varchar(50),
	MaHangHoa	varchar(50),
	TenHangHoa	nvarchar(200),
	SoLuong	int,
	PhanTramChietKhau	float,
	PhanTramChietKhauThuongMai float,
	DonGia	float,
	ThanhTien	float,
	Thue	float,
	HanSuDung	datetime,
	GhiChu	nvarchar(200),
	Deleted	bit
)
GO
create table DonViTinh
(
	DVTID	int	primary key identity,
	MaDonViTinh	varchar(50) unique not null,
	TenDonViTinh	nvarchar(200) not null,
	GhiChu	nvarchar(200),
	Deleted	bit	
)
go
create table HoaDonNhap
(
	HoaDonNhapID	int	primary key identity,
	MaHoaDonNhap	varchar(50) unique	Not Null,
	NgayNhap	Datetime,
	MaNhaCungCap	varchar(50) not null,
	TenNhaCungCap nvarchar(200),
	NoHienThoi	float,
	NguoiGiaoHang	nvarchar(200)	,
	HinhThucThanhToan	nvarchar(20),
	MaKho	varchar(50),
	TenKho nvarchar(200),
	HanThanhToan	Datetime,
	MaDonDatHang	varchar(50)	null,
	NgayDat datetime,
	MaTienTe	varchar(50),
	TyGiaTienTe		float,
	ChietKhau	float,
	ThanhToanNgay	float	,
	ThueGTGT	varchar(50),
	TongTien	float,
	ThanhToanSauKhiLapPhieu float,
	MaNhanVien	varchar(50),
	TenNhanVien	nvarchar(200),
	GhiChu	nvarchar(200),
	Deleted	bit	
)
go
create table ChiTietHoaDonNhap
(
	ChiTietHoaDonNhapID	int	primary key identity,
	MaHoaDonNhap	varchar(50) not null,
	MaHangHoa	varchar(50) not null,
	TenHangHoa nvarchar(200),
	SoLuong	float	Not Null,
	PhanTramChietKhau	float,
	DonGia	float,
	ThanhTien	float,
	Thue	float,
	SoLuongConLai float,
	HanSuDung	datetime,
	MaDonViTinh varchar(50),
	TenDonViTinh nvarchar(200),
	GhiChu	nvarchar(200),
	Deleted	bit	
)
go
create table KMThuChi
(
	ThuChiID	int	primary key identity,
	MaKhoanMuc	varchar(50) Not Null, 
	TenKhoanMuc	nvarchar(200),
	LoaiKM	bit,
	DoiTuong	nvarchar(200),
	NoTK	varchar(50),
	CoTK	varchar(50),
	GhiChu	nvarchar(200),
	Deleted	bit	
)

go 
create table TienTe
(
	TienteID	int	primary key identity,
	MaTienTe	varchar(50) unique	Not Null,
	TenTienTe	nvarchar(200)	Not Null,
	BieuTuong	varchar(10),
	TyGia   float Not Null,
	GhiChu	nvarchar(200),
	Deleted	bit	
)		 
go				
create table HDBanBuon
(
	HDBanBuonID	int	primary key identity,
	MaHDBanBuon	varchar(50) unique	Not Null,	
	NgayLap	Datetime,	
	MaKhachHang	varchar(50),
	TenKhachHang	nvarchar(200),
	NoHienThoi	float	,
	NguoiNhanHang	nvarchar(200),	
	MaDonDatHang varchar(50),
	MaKho	varchar(50),
	TenKho nvarchar(200),
	HanThanhToan	Datetime,
	MaNhanVien	varchar(50),
	TenNhanVien nvarchar(200),
	PhanTramChietKhauThuongmai	float,
	ChietKhau	float,
	ThanhToanNgay	float,
	ThanhToanSauKhiLapPhieu	float,
	ThueGTGT	varchar(50),
	TongTienThanhToan	float,
	MaTheVip varchar(50),
	GiaTriThe float,
	GhiChu	nvarchar(200),
	Deleted	bit	
)
go
create table ChiTietHDBanBuon
(
	ChiTietHDBanBuonID	int	primary key identity,
	MaHDBanBuon	varchar(50) not null,
	MaHangHoa	varchar(50) not null,
	TenHangHoa nvarchar(200),
	SoLuong	int not null,
	DonGia float,
	PhanTramChietKhau	float,
	ThueGTGT float,
	ThanhTien float,
	SoLuongConLai float,
	HanSuDung	datetime,
	GhiChu	nvarchar(200),
	Deleted	bit
)
go
create table HDBanLe
(
	HDBanLeID	int primary key identity,
	MaHDBanLe	varchar(50) unique not null,
	NgayLap	Datetime,
	MaKhachHang	varchar(50),
	TenKhachHang	nvarchar(200),
	MaKho	varchar(50),
	TenKho  nvarchar(200),
	MaNhanVien	varchar(50),
	TenNhanVien nvarchar(200),
	ChietKhau	float,
	ThueGTGT	varchar(50),
	TongTienThanhToan	float,
	MaTheVip	varchar(50),
	GiaTriThe	float,
	Deleted	bit
)
go
create table ChiTietHDBanle
(
	ChiTietHDBanleID	int primary key identity,
	MaHDBanLe	varchar(50) not null,
	MaHangHoa	varchar(50) not null,
	TenHangHoa nvarchar(200),
	SoLuong	int,
	DonGia float,
	PhanTramChietKhau	float,
	ThueGTGT float,
	ThanhTien float,
	SoLuongConLai float,
	HanSuDung	datetime,
	GhiChu	nvarchar(200),
	Deleted	bit
)
go 
create table TraLaiNCC
(
	TraLaiNCCID	int primary key identity,
	MaHDTraLaiNCC	varchar(50) unique not null,
	MaNhanVien	varchar(50),
	Ngaytra	Datetime,
	MaNCC	varchar(50),
	TenNCC	nvarchar(200),
	NoHienThoi	float,
	NguoiNhanHang	nvarchar(200),
	MaKho	varchar(50),
	TenKho nvarchar(200),
	TienBoiThuong	float,
	ThanhToanNgay	float,
	ThanhToanSauKhiLapPhieu	float,
	ThueGTGT	float,
	GhiChu	nvarchar(200),
	Deleted	bit
)
go 
create table ChiTietTraLaiNCC
(
	ChiTietTraLaiNCCID	int primary key identity,
	MaHDTraLaiNCC	varchar(50) not null,
	MaHangHoa	varchar(50) not null,
	TenHangHoa nvarchar(200),
	MaHoaDon	varchar(50) not null,
	SoLuong	int not null,
	PhanTramChietKhau	float,
	ChietKhauHoaDon float,
	DonGia	float,
	ThanhTien	float,
	Thue	float,
	HanSuDung	datetime,
	GhiChu	nvarchar(200),
	Deleted	bit
)
go
create table PhieuDieuChuyenKho
(
	PhieuDieuChuyenKhoID	int primary key identity,
	MaPhieuDieuChuyenKho	varchar(50) unique not null,
	NgayDieuChuyen	Datetime,
	TuKho	varchar(50),
	TenKhoDi nvarchar(200),
	DenKho	varchar(50),
	TenKhoDen nvarchar(200),
	MaHoaDonNhap	varchar(50),
	XacNhan	bit,
	MaNhanVien	varchar(50),
	GhiChu	nvarchar(200),
	Deleted	bit
)
go
create table ChiTietPhieuDieuChuyenKho
(
	ChiTietPhieuDieuChuyenKhoID	int primary key identity,
	MaPhieuDieuChuyenKho	varchar(50) not null,
	MaHangHoa	varchar(50) not null,
	TenHangHoa nvarchar(200),
	SoLuong	int,
	DonGia float,
	ThanhTien float,
	GhiChu	nvarchar,
	Deleted	bit
)
go
create table KiemKeKho
(
	PhieuKiemKeKhoID	int primary key identity,
	MaPhieuKiemKe	varchar(50) unique not null,
	NgayKiemKe	Datetime,
	MaKho	varchar(50),
	TenKho	nvarchar(200),
	MaNhanVien	varchar(50),
	GhiChu	nvarchar(200),
	Deleted	bit
)
go
create table ChiTietKiemKeKho
(
	ChiTietKiemKeKhoID	int primary key identity,
	MaPhieuKiemKe	varchar(50) not null,
	MaHangHoa	varchar(50) not null,
	TenHangHoa nvarchar(200),
	TonThucTe	int,
	TonSoSach	int,
	LyDo	nvarchar(200),
	Deleted	bit
)
go
create table PhieuTTCuaKH
(
	PhieuTTCuaKHID	int primary key identity,
	MaPhieuTTCuaKH	varchar(50) unique not null,
	NgayLap	Datetime,
	MaKhachHang	varchar(50),
	TenKhachHang nvarchar(200),
	MaNhanVien varchar(50),
	MaKho varchar(50),
	TongThanhToan	float, 
	NoHienThoi float,
	NguoiNop	nvarchar(200),
	LoaiPhieu bit,
	GhiChu	nvarchar(200),							 
	Deleted	bit
)
go
create table ChiTietPhieuTTCuaKH
(
	ChiTietPhieuTTCuaKHID	int primary key identity,
	MaHoaDon	varchar(50) not null,
	MaPhieuTTCuaKH	varchar(50) not null,
	TongTien	float,
	TienNo	float,
	ThanhToan	float,											 
	GhiChu	nvarchar(200),						
	Deleted	bit
)
go
create table PhieuTTNCC
(
	PhieuTTNCCID	int primary key identity,
	MaPhieuTTNCC	varchar(50) unique not null,
	NgayLap	Datetime,
	MaNhaCungCap	varchar(50),
	TenNhaCungCap	nvarchar(200),
	TongThanhToan	float,
	NoHienThoi float,
	Nguoinhan	nvarchar(200),
	MaNhanVien	varchar(50),
	MaKho	varchar(50),
	LoaiPhieu bit,
	GhiChu	nvarchar(200),
	Deleted	bit
)
go
create table ChiTietPhieuTTNCC
(
	ChiTietPhieuTTNCCID	int primary key identity,
	MaHoaDon	varchar(50) not null,
	MaPhieuTTNCC	varchar(50) not null,
	TongTien	float,
	TienNo	float,
	ThanhToan	float,
	GhiChu	nvarchar(200),
	Deleted	bit
)
go
create table PhieuThu
(
	PhieuThuID	int primary key identity,
	MaPhieuThu	varchar(50) unique not null,
	NgayLap	Datetime,
	MaDoiTuong	varchar(50),
	KhoanMuc	nvarchar(200),
	DoiTuong	nvarchar(20),
	TenDoiTuong nvarchar(200),
	DiaChi	nvarchar(200),
	MaSoThue	varchar(50),
	NguoiNopTien	nvarchar(200),
	MaTKNganHang	varchar(50),
	CoTaiKhoan varchar(50),
	NoTaiKhoan	varchar(50),
	TongTienThanhToan	float,
	MaKho	varchar(50),
	MaNhanVien varchar(50),
	GhiChu	nvarchar(200),
	Deleted	bit
)
go
create table PhieuChi
(
	PhieuChiID	int	primary key identity,
	MaPhieuChi	varchar(50) unique not null,
	NgayLap	Datetime,
	MaDoiTuong	varchar	(50),
	KhoanMuc	nvarchar(200),
	DoiTuong	nvarchar(20),
	TenDoiTuong	nvarchar(200),
	DiaChi	nvarchar	(50),
	MaSoThue	varchar(50),
	NguoiNhanTien	nvarchar(200),
	MaTKNganHang	varchar(50),
	CoTaiKhoan	varchar(50),
	NoTaiKhoan varchar(50),
	TongTienThanhToan	float,
	MaKho	varchar(50),
	MaNhanVien	varchar(50),
	GhiChu	nvarchar(200),
	Deleted	bit	
)

go
create table PhieuXuatHuy
(
	PhieuXuatHuyID	int primary key identity,
	MaPhieuXuatHuy	varchar(50) unique not null,
	NgayLap	Datetime,
	MaNhanVien	varchar(50),
	TenNhanVien nvarchar(200),
	MaKho	varchar(50),
	TenKho nvarchar(200),
	TrangThai	bit,
	Tongtien	float,
	GhiChu	nvarchar(200),
	Deleted	bit
)
go
create table ChiTietXuatHuy
(
	ChiTietXuatHuyID	int primary key identity,
	MaPhieuXuatHuy	varchar(50) not null,
	MaHangHoa	varchar(50) not null,
	TenHangHoa nvarchar(200),
	SoLuong	int,
	GiaNhap float,
	HanSuDung	datetime,
	GhiChu	nvarchar(200),
	Deleted	bit
)
go
create table DiemThuongKhachHang
(
	DiemThuongKhachHangID	int primary key identity,
	MaDiemThuongKhachHang	varchar(50) unique not null,
	MaKhachHang	varchar(50) not null,
	TenKhachHang	nvarchar(200),
	TongDiem	int,
	DiemDaDung	int,
	GhiChu	nvarchar(200),
	Deleted	bit
)
go
create table TyLeTinh
(
	TyLeTinhID	int primary key identity,
	MaTyLeTinh	varchar(50) unique not null,
	SoTien	float,
	NgayNhap	DateTime,
	GhiChu	nvarchar(200),
	Deleted	bit
)
go
create table LoaiTheVip
(
	LoaiTheVipID	int primary key identity,
	MaLoaiTheVip	varchar(50) unique not null,
	TenLoaiThe	nvarchar(200),
	GiaTriThe	float,
	SoDiemThuong	float,
	GhiChu	nvarchar(200),
	Deleted	bit
)
GO
create table TheVip
(
	TheVipID	int primary key identity,
	MaTheVip	varchar(50) unique not null,
	MaKhachHang	varchar(50) not null,
	TenKhachHang	nvarchar(200),
	LoaiThe	varchar(50),
	GiaTriConLai	float,
	GhiChu	nvarchar(200),
	Deleted	bit
)
go
create table CongTy
(
	CongTyID int primary key identity,
	MaCongTy varchar(50),
	TenCongTy nvarchar(200),
	DiaChi	nVarchar(200),
	Email	nvarchar(50),
	Website	nvarchar(200),
	DienThoai	varchar(20),
	Fax	varchar	(20),
	LinkImage nvarchar(200),
	GhiChu nvarchar(200)
)
go
CREATE TABLE NhomQuyen
(
	NhomQuyenID INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
	TenNhomQuyen nVARCHAR(50) NOT NULL UNIQUE,
	isDeleted bit default 1
	
)
GO	   
CREATE TABLE TaiKhoan
(
	TenDangNhap nVARCHAR(200) NOT NULL PRIMARY KEY,
	MatKhauDangNhap nVARCHAR(200) NOT NULL,
	KhoaTaiKhoan BIT NULL,
	TenNhanVien nvarchar(200),
	MaNhanVien nVARCHAR(20) NULL,
	Administrator BIT NULL,
	TenNhomQuyen nvarchar(50) NOT NULL
)
GO
CREATE TABLE  Quyen
(
	QuyenID INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
	TenForm nVARCHAR(50) NOT NULL UNIQUE,
	Ten nvarchar(100) not null
)
GO
CREATE TABLE ChiTietQuyen
(
	ChiTietQuyenID INT IDENTITY PRIMARY KEY,
	TenNhomQuyen nvarchar(50) NOT NULL,
	TenForm nvarchar(50) NOT NULL,
	QuyenThem BIT NULL,
	QuyenSua BIT NULL,
	QuyenXoa BIT NULL,
	QuyenXem BIT NULL
)
GO
create table KhuyenMaiTangHang
(
	KhuyenMaiTangHangID int identity primary key,
	MaKhuyenMaiTangHang varchar(50) unique not null,
	TenDotKhuyenMai nvarchar(200) not null,
	NgayLap datetime not null,
	NgayBatDau datetime not null,
	NgayKetThuc datetime not null,
)
GO
create table ChiTietKhuyenMaiTangHang
(
	ChiTietKhuyenMaiTangHangID int identity primary key,
	MaKhuyenMaiTangHang varchar(50) not null,
	MaHangKhuyenMai varchar(50) not null,
	TenHangKhuyenMai nvarchar(200) not null,
	SoLuongKhuyenMai int not null,
	MaHangDuocKhuyenmai varchar(50) not null,
	TenHangDuocKhuyenmai nvarchar(200) not null, 
	SoLuongDuocKhuyenMai int not null,	
)
GO
create table QuyDoiDonViTinh
(
	QuyDoiDonViTinhID int identity primary key,
	MaQuyDoiDonViTinh varchar(50) not null,
	MaHangQuyDoi varchar(50) not null,
	TenHangQuyDoi nvarchar(200) not null,
	MaDonViTinh varchar(50) not null,
	SoLuongQuyDoi int not null,
	MaHangDuocQuyDoi varchar(50) not null,
	TenHangDuocQuyDoi nvarchar(200) not null,
	MaDonViTinhDuocQuyDoi varchar(50) not null,
	SoLuongDuocQuyDoi int not null,
	HanSuDung	datetime,
	Deleted bit
)
go
create table GoiHang
(
	GoiHangID	int		identity	primary key,
	MaGoiHang	varchar (50) not null,
	TenGoiHang	nvarchar	(200),
	MaNhomHang varchar(50),
	TenNhomHang nvarchar(200),
	GiaNhap float,
	GiaBanBuon float,
	GiaBanLe float,
	HanSuDung	datetime,
	GhiChu nvarchar(200),
	Deleted bit
)
go
create table ChiTietGoiHang
(
	ChiTietGoiHangID	int	identity(1,1)primary key,
	MaGoiHang	varchar (50) not null,
	MaHangHoa	varchar(50) not null,
	TenHangHoa	nvarchar(200),
	SoLuong	float,
	GiaNhap float,
	GiaBanBuon float,
	GiaBanLe float,
	HanSuDung	datetime,
	Deleted bit
)
go
CREATE TABLE SoDuCongNo
(
	SoDuCongNoID int identity(1,1) primary key,
	MaSoDuCongNo varchar(20),
	MaDoiTuong varchar(20),
	TenDoiTuong nvarchar(200),
	DiaChi nvarchar(200),
 	SoDuDauKy float,
	NgayKetChuyen datetime,
	SoDuCuoiKy float,
	LoaiDoiTuong bit,
		MaKho varchar(50),
	TrangThai bit
)
go
CREATE TABLE SoDuSoQuy
(
	SoDuSoQuyID int identity(1,1) primary key,
	MaSoDuSoQuy varchar(20),
 	SoDuDauKy float,
	NgayKetChuyen datetime,
	SoDuCuoiKy float,
	MaKho varchar(50),
	TrangThai bit
)
go
create table TongHopXNT
(
	 TongHopXNTID int identity(1,1) primary key,
	 MaTongHopXNT varchar(50),
	 MaKho varchar(50),
	 MaHangHoa varchar(50),
	 TonDauKySL float,
	 TonDauKyGT float,
	 NhapSL float,
	 NhapGT float,
	 XuatSL float,
	 XuatGT float,
	 TonCuoiKySL float,
	 TonCuoiKyGT float,
	 NgayKetChuyen Datetime,
	 TrangThai bit

)
go
create proc sp_LayID
	 @table nvarchar(100)
as
begin
	 declare @col nvarchar(100),@col1 nvarchar(100), @sql nvarchar(150)
	 set @col = (select column_name from information_schema.columns where table_name = @table and ordinal_position=2)
	 set @sql= N'select top(1)['+@col+'] as ID from ['+@table+'] order by ['+@col+']'+ 'desc'
	 exec sp_executesql @sql
end
go
create proc sp_LayCombox
	 @tableName nvarchar(20),
	 @columnID nvarchar(20),
	 @columnName nvarchar(20)
as
begin
	 declare @sql nvarchar(150)
	 set @sql= N'select distinct ['+@columnID+'] as ID,['+@columnName+'] from ['+@tableName+']  Where Deleted=N''False'' order by '+ @columnName +' ASC'
	 exec sp_executesql @sql
end
GO

--==========================Data Test==================================
GO
Insert Into KhoHang values('MK_0001',N'Kho bán buôn',N'Hà Nội',N'098434443',N'Ngô Đình Diệm','NV_0001',N'','False')
Insert Into KhoHang values('MK_0002',N'Kho bán lẻ',N'Hà Nội',N'0433678992',N'Tiểu Long Nữ','NV_0002',N'','False')
GO
--=====================================================================
